﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CommonDB
{
    /// <summary>
    /// 对外提供数据类
    /// </summary>
    public class DataProviderImpl
    {
        private static readonly log4net.ILog _logger
            = log4net.LogManager.GetLogger(typeof(DataProviderImpl));

        private readonly IDbProvider _dbProvider;
        private readonly IDbOperator _dbOperator;

        /// <summary>
        /// 默认初始化Sqlite数据库操作类
        /// </summary>
        public DataProviderImpl()
            : this(new SqliteOperator())
        {
        }
        /// <summary>
        /// 可以构造任意数据库操作类
        /// </summary>
        public DataProviderImpl(IDbOperator dbOperator)
        {
            _dbOperator = dbOperator;
            _dbProvider = _dbOperator.DbProvider;
        }

        /// <summary>
        /// 获取数据测试方法
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTest()
        {
            string sql = @"SELECT * FROM COMPANY ORDER BY ID";

            return _dbOperator.ExecuteTable(sql);
        }

        /// <summary>
        /// 单条插入数据测试方法
        /// 经测试插入1000条数据耗时24秒，插入10000条数据耗时291秒
        /// </summary>
        /// <returns></returns>
        public int InsertDataTest(string id, string name, string age, string address, string salary)
        {
            string sql = @"INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY) 
                        VALUES(@ID,@Name,@Age,@Address,@Salary)";

            IDataParameter[] parameters = {
                new SQLiteParameter("@ID", DbType.UInt16),
                new SQLiteParameter("@Name", DbType.String),
                new SQLiteParameter("@Age", DbType.UInt16),
                new SQLiteParameter("@Address", DbType.String),
                new SQLiteParameter("@Salary", DbType.Currency)
            };
            int num = -1;
            for (int i = 1; i < 1000; i++)
            {
                parameters[0].Value = i + 10;
                parameters[1].Value = name;
                parameters[2].Value = age;
                parameters[3].Value = address;
                parameters[4].Value = salary;
                num = _dbOperator.ExecuteNonQuery(sql, parameters);
            }
            return num;
        }

        /// <summary>
        /// 批量插入数据测试方法
        /// 经测试插入1000条数据耗时0.037秒，插入10000条数据耗时0.554秒
        /// </summary>
        /// <returns></returns>
        public void InsertBatchDataTest(string id, string name, string age, string address, string salary)
        {
            string sql = @"INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY) 
                        VALUES(@ID,@Name,@Age,@Address,@Salary)";

            IDataParameter[] parameters = {
                new SQLiteParameter("@ID", DbType.UInt32),
                new SQLiteParameter("@Name", DbType.String),
                new SQLiteParameter("@Age", DbType.UInt32),
                new SQLiteParameter("@Address", DbType.String),
                new SQLiteParameter("@Salary", DbType.Currency)
            };
            try
            {
                _dbProvider.Connection.Open();
                _dbProvider.BeginTransaction();
                for (int i = 0; i < 10000; i++)
                {
                    parameters[0].Value = i + 10;
                    parameters[1].Value = name;
                    parameters[2].Value = age;
                    parameters[3].Value = address;
                    parameters[4].Value = salary;
                    _dbOperator.TransExecuteNonQuery(sql, parameters);
                }
                _dbProvider.Commit();
            }
            catch (Exception ex)
            {
                _dbProvider.RollBack();
                _logger.Error(ex.Message, ex);
            }
            finally
            {
                _dbProvider.Connection.Close();
            }
        }
    }
}
